﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Grishko.SerilizeTools;
using Grishko.DataTier.MetaInfo;
using System.ComponentModel;
using System.Reflection;
using System.Collections;

namespace Grishko.DataTier.SqlSentence
{

    /// <summary>
    /// Основной класс по формированию скрипта запроса
    /// </summary>
   [IsNotSrchStorageLinksObject]  
   public class ObjectWhereSentence:EntitySingle
   {
       public class AddSrchObjects
       {
           public Type EntityType { get; set; }
           public  string SqlFunction { get;set;}
           /// <summary>
           /// Перечень пар - свойство - соотв. тип.
           /// </summary>
           private Hashtable _PropertyCollection = new Hashtable();
           public Hashtable PropertyCollection { get { return _PropertyCollection; } }
           
           /// <summary>
           /// Свойства - исключаемые из списка доступных в операциях select
           /// </summary>
           public string[] BanProperties { get; set; }
           
       }


       private List<WhereSentenceWord> _MandatoryConditions = new List<WhereSentenceWord>();

       /// <summary>
       /// Обязательные условия - колекция WhereSentenceWord
       /// </summary>
       protected List<WhereSentenceWord> MandatoryConditions
       {
           get {  return _MandatoryConditions; }

       }

       public Type EntityType
       {
           get{return EntityTypeInfo.EntityType;  }
           set { EntityTypeInfo.EntityType = value; }
       }

       public ObjectWhereSentence(Type valEntityType)
       {
           EntityType = valEntityType;
       }


       private AddSrchObjects __EntityTypeInfo = new AddSrchObjects()
       {
           SqlFunction = "GetObjectPropertyStorage()"
       };
       /// <summary>
       /// Объект - описывающий функцию - поставщика поискового пространства и коллекцию дополнительных объектов для поиска
       /// </summary>
       public virtual AddSrchObjects EntityTypeInfo
       {
           get 
           {
               return __EntityTypeInfo;               

           }
           set
           {
               __EntityTypeInfo = value;
           } 
       }

       
       public ObjectWhereSentence()
       {
           
       }


       #region Константы скриптов
       const string Script_SrchRoot = "#SrchRoot";
       /// <summary>
       /// Sql функция - поставщик ObjectPropertyStorage=EntityType.SqlFunction
       /// </summary>
       protected  string Script_SrchObjectPropertyStorage
       {
           get { return EntityTypeInfo.SqlFunction; }
       }

       /// <summary>
       /// Paging StartPageRow 
       /// </summary>
       private int _StartPageRow = 1;
       public int StartPageRow { get { return _StartPageRow; } set { _StartPageRow  = value; } }
       
       /// <summary>
       /// Paging  EndPageRow 
       /// </summary>
       private int _EndPageRow = 100;
       public int EndPageRow { get { return _EndPageRow; } set { _EndPageRow = value; } }

       #endregion       

       #region Property
       /// <summary>
       /// Соответсвует TagName EntityType
       /// </summary>
    
       public virtual string EntityTypeTagName 
       {
           get
           {
               //Это поле пишется в базу поиска
               object[] arr = EntityType.GetCustomAttributes(typeof(TagNameInfo), true);
               if (arr.Length == 0)
                   throw new Exception(string.Format("ObjectWhereSentence.TagName нет TagNameInfo у типа {0}",
                                                      new string[] { EntityType.ToString () }));

               return ((TagNameInfo)arr[0]).TagName;
           }

       }
              

       /// <summary>
       /// Дополнительные непараметризированные like
       /// </summary>
       public string Lexeme { get; set; }

       private WhereSentenceWord _Sentence = new WhereSentenceWord() { WordType = WhereSentenceWord.WhereSentenceWordTypes.AND};
       
       
       /// <summary>
       /// Основной носитель условий 
       /// </summary>
       public WhereSentenceWord Sentence
       {
           get 
           {
               return _Sentence; 
           }
       }
       #endregion

       #region Public Method
       /// <summary>
       /// Пройтись по типу и сформировать набор возможных полей предложения
       /// </summary> 
       //public List<WhereSentenceWord.SentenceWordField> GetSentenceDictionary()
       //{          
       //    Func<string,bool> InBanList=delegate (string PropertyName)
       //    {
       //        foreach (string s in this.EntityTypeInfo.BanProperties)
       //        { 
       //            if(PropertyName.IndexOf (s,0)>-1)
       //                             return true; 
       //        }
       //        return false ;
       //    };

       //    List<Word> =SrchInfo.GetSentenceDictionary(EntityType);           
           
       //    ///Пройтись и сформировать дополнительные поисковые свойства                      
       //    foreach ( DictionaryEntry itm in  this.EntityTypeInfo.PropertyCollection  )
       //    {
       //        foreach (WhereSentenceWord.SentenceWordField f in SrchInfo.GetSentenceDictionary((Type)itm.Value))               
       //        {
       //            if (InBanList(f.ID))
       //                continue;
                    
       //            f.ID = itm.Key.ToString() + "." + f.ID;
       //             Result.Add(f);
       //        }               
               
       //    }
       //    return Result;

       //}



       /// <summary>
       /// Вернуть  ключи словаря поисковых полей
       /// </summary>
       /// <returns></returns>
       public List<String> GetSentenceDictionaryKeys()
       {
           return SrchInfo.GetSentenceDictionaryKeys(EntityType);           
       }

       /// <summary>
       /// Ветнуть тектовые ключи словаря поисковых полей
       /// </summary>
       /// <returns></returns>
       public List<String> GetSentenceDictionaryTxtKeys()
       {
           return SrchInfo.GetSentenceDictionaryTxtKeys(EntityType);
       }

       /// <summary>
       /// Вернуть поле по ключу
       /// </summary>
       /// <param name="FieldKey"></param>
       /// <returns></returns>
       public WhereSentenceWord.SentenceWordField GetSentenceDictionaryField(string FieldKey)
       {
           WhereSentenceWord.SentenceWordField result = null;
           //List<WhereSentenceWord.SentenceWordField> Res = GetSentenceDictionary();
           //for (int i = 0; i < Res.Count; i++)
           //{
           //    if (Res[i].ID == FieldKey)
           //    {
           //        result = Res[i];
           //        break;
           //    }
           //}
           return result;               

       }

       /// <summary>
       /// Сформировать поле предложения по ключу поля и типу опреации
       /// </summary> 
       public WhereSentenceWord.SentenceWordField GetSentenceDictionaryField(string FieldKey,WhereSentenceWord.SentenceWordOperatorTypes OperationType )
       {
           WhereSentenceWord.SentenceWordField result=null ;
           result = GetSentenceDictionaryField(FieldKey);
           SetValueType(result, OperationType);
           return result ;
   

       }
       /// <summary>
       /// Установить Value - соответсвующее опреации
       /// </summary>
       /// <param name="Field"></param>
       /// <param name="OperationType"></param>
       public static void SetValueType(WhereSentenceWord.SentenceWordField Field, WhereSentenceWord.SentenceWordOperatorTypes OperationType)
       {
           switch (Field.PropertyType)
           {
               case SrchItemInfo.PropertyControlType.Text:                   
                       Field.Value = new WhereSentenceWord.WordFieldValue_StringBetween();
                   break;

               case SrchItemInfo.PropertyControlType.Number:
                   if (OperationType == WhereSentenceWord.SentenceWordOperatorTypes.BETWEEN)
                       Field.Value = new WhereSentenceWord.WordFieldValue_NumberBetween();
                   else
                       Field.Value = new WhereSentenceWord.WordFieldValue_Number();
                   break;

               case SrchItemInfo.PropertyControlType.Date:
                   if(OperationType == WhereSentenceWord.SentenceWordOperatorTypes.BETWEEN)  
                            Field.Value = new WhereSentenceWord.WordFieldValue_DateBetween();
                    else
                       Field.Value = new WhereSentenceWord.WordFieldValue_Date();
                   break;

               case SrchItemInfo.PropertyControlType.Bool:
                        Field.Value = new WhereSentenceWord.WordFieldValue_Bool();
                   break;
           }
           Field.OperationType = OperationType;
       }


       /// <summary>
       /// конвертировать строчку в значение WhereSentenceWordTypes  (AND/OR)
       /// </summary>
       /// <param name="sKey"></param>
       /// <returns></returns>
       public static WhereSentenceWord.WhereSentenceWordTypes ConverStringToWordTypes(string sKey)
       {
           if (WhereSentenceWord.WhereSentenceWordTypes.AND.ToString().ToUpper().Equals(sKey.ToUpper()))
               return WhereSentenceWord.WhereSentenceWordTypes.AND;
           
           return WhereSentenceWord.WhereSentenceWordTypes.OR ;
       }


       /// <summary>
       /// конвертировать строчку в значение SentenceWordOperatorTypes  (EQUALLY/MORE/LESS ....)
       /// </summary>
       public static WhereSentenceWord.SentenceWordOperatorTypes ConverStringToOperatorType(string sKey)
       { 
           if(WhereSentenceWord.SentenceWordOperatorTypes.NOT_EQUALLY .ToString ().ToUpper().Equals(sKey.ToUpper ()))
                return WhereSentenceWord.SentenceWordOperatorTypes.NOT_EQUALLY ;

           if (WhereSentenceWord.SentenceWordOperatorTypes.MORE .ToString().ToUpper().Equals(sKey.ToUpper()))
               return WhereSentenceWord.SentenceWordOperatorTypes.MORE;

           if (WhereSentenceWord.SentenceWordOperatorTypes.LESS.ToString().ToUpper().Equals(sKey.ToUpper()))
               return WhereSentenceWord.SentenceWordOperatorTypes.LESS;

           if (WhereSentenceWord.SentenceWordOperatorTypes.LIKE .ToString().ToUpper().Equals(sKey.ToUpper()))
               return WhereSentenceWord.SentenceWordOperatorTypes.LIKE;

           if (WhereSentenceWord.SentenceWordOperatorTypes.EQUALLY.ToString().ToUpper().Equals(sKey.ToUpper()))
               return WhereSentenceWord.SentenceWordOperatorTypes.EQUALLY;

           if (WhereSentenceWord.SentenceWordOperatorTypes.BETWEEN.ToString().ToUpper().Equals(sKey.ToUpper()))
               return WhereSentenceWord.SentenceWordOperatorTypes.BETWEEN;
           
           return WhereSentenceWord.SentenceWordOperatorTypes.EQUALLY;
           
       }

       /// <summary>
       /// Формирует набор слов ипа
       /// </summary>
       /// <returns></returns>
       private static List<WhereSentenceWord> GetWhereSentenceWordsFromString(string value)
       {
                      

           List<WhereSentenceWord> result = new List<WhereSentenceWord>();
           if (string.IsNullOrEmpty(value))
               return result;
           
           string[] arr= value.Split(" ".ToCharArray ()[0]) ;
           for (int i = 0; i < arr.Length; i++)
           {
               if (arr[i] == " ")
                    continue;
               WhereSentenceWord w = new WhereSentenceWord() { WordType = WhereSentenceWord.WhereSentenceWordTypes.AND };
               //WhereSentenceWord.SentenceWordField F1 = GetSentenceDictionaryField(FieldId, WhereSentenceWord.SentenceWordOperatorTypes.EQUALLY);
               WhereSentenceWord.SentenceWordField F1 = new WhereSentenceWord.SentenceWordField();
               F1.OperationType = WhereSentenceWord.SentenceWordOperatorTypes.LIKE;
               F1.PropertyType = SrchItemInfo.PropertyControlType.OnlyValueSrch;
               F1.Value = new WhereSentenceWord.WordFieldValue() { Value = arr[i] };
               w.WordFields.Add(F1);
               result.Add(w);
           }
           return result;
           
       }

       
       /// <summary>
       /// Удалить временные (не хранимые условия фильтра MandatoryConditions, )
       /// </summary>
       private void ClearTempCondition()
       {           
           
           foreach (WhereSentenceWord i in MandatoryConditions)
           {
               this.Sentence.ChildNodes.Remove(i.ID);
           }
                      
           List<string> l = new List<string>();
           Sentence.ChildNodes.Reset();
           foreach (WhereSentenceWord w in this.Sentence.ChildNodes)
           {
               if (w.ImTmp)
               {
                   l.Add (w.ID);
               }
           }

           foreach (string s in l)
               this.Sentence.ChildNodes.Remove(s);
           
           
       }

       public virtual string GetScript ()
       {

           try
           {
               foreach (WhereSentenceWord i in MandatoryConditions)
               {
                   this.Sentence.ChildNodes.Add(i);
               }

               ///Если существует что нибудь в Lexeme - вставляем как усовие
               foreach (WhereSentenceWord i in GetWhereSentenceWordsFromString(Lexeme))
               {
                   i.ImTmp = true;
                   this.Sentence.ChildNodes.Add(i);
               }


               StringBuilder Writer = new StringBuilder();
               Script_DeclareVars_0(Writer);                        //Декларировать переменные

               ///если у запрос пустой - выбрать все объекты
               if (Sentence.GetAllWordFields().Count == 0)
               {
                   Script_PopulateRootOfPropertyTree_1(Writer);
                   Writer.Append(" INSERT INTO @SelectedObjectId SELECT ObjectID FROM #SrchRoot ");
                   Script_PopulatePage_5(Writer);
                   Script_GetObjects_6(Writer);
                   return Writer.ToString();
               }


               Script_PopulateTreeValueFields_01(Writer);           // Срез по древовидным свойствам
               Script_PopulateRootOfPropertyTree_1(Writer);         //Вершина дерева
               Script_PopulatePropertyTree_2(Writer);               //Заполнение дерева
               Script_UpadateTreeValueProperty_3(Writer);           //Отметить - ok - значения древовидных свойств
               Script_PopulateTargetObjectSet_4(Writer);            //Конечная выборка ID           

               //Непараметризированая часть Like - есть?            
               if (Sentence.GetAllWordFields(SrchItemInfo.PropertyControlType.OnlyValueSrch).Count > 0)
                   Script_PopulatePage_41(Writer);


               Script_PopulatePage_5(Writer);                       //Разбивка Id на страницы
               Script_GetObjects_6(Writer);                       //Конечная выборка объектов           

               return Writer.ToString();
           }
           finally
           {
               ClearTempCondition();
           }

       }
       #endregion


       /// <summary>
       /// Сформировать Корень дерева PropertyStorageTree
       /// </summary>
       /// <returns></returns>
       protected virtual void Script_DeclareVars_0(StringBuilder Writer)
       {
           Writer.Append (" DECLARE @PopulatedObjectTree TABLE (ObjectID varchar(36), PropertyName varchar(max) ,TxtProperty varchar(max), PropertyValue varchar(max),ValueIsSqlFunction bit)" + Environment.NewLine);
           Writer.Append("DECLARE @PropertyArray TABLE (ObjectId varchar(36), 	Property varchar(max), ItemCount int)" + Environment.NewLine);
           Writer.Append("DECLARE @SelectedObjectId TABLE (ObjectId varchar(36))" + Environment.NewLine);
           Writer.Append("DECLARE @SelectedObjectId_ TABLE (ObjectId varchar(36))" + Environment.NewLine);
           Writer.Append("DECLARE @TreePropertyCollection  TABLE (ObjectId varchar(36), TeeValueId  varchar(36) )" + Environment.NewLine);           

       }
       
       /// <summary>
       /// Сформировать выборку по TreeValue полям 
       /// </summary>
       /// <returns></returns>
       protected virtual void Script_PopulateTreeValueFields_01(StringBuilder Writer)
       {
           Func<WhereSentenceWord.SentenceWordField,string> GetFieldOrConditions = delegate(WhereSentenceWord.SentenceWordField Field)
           {
               WhereSentenceWord.WordFieldValue_TreeValue TreeValue= (WhereSentenceWord.WordFieldValue_TreeValue ) Field.Value;
                    
               StringBuilder sb= new StringBuilder ();
               sb.Append (" PropertyValue='"+TreeValue.Value.ID +"' ");
                              
               foreach (Grishko.DataTier.DictionaryItem  item in TreeValue.Value.ChildNodes)
               {
                   sb.Append(" OR ");
                   sb.Append("PropertyValue='"+item.Value +"' ");

               }
               return sb.ToString ();
           };

           ///выбираем - запрошенные свойства по TreePropertyValue
           List<WhereSentenceWord.SentenceWordField> l = Sentence.GetAllWordFields();
           var TreeFields=
                from n in l
                where 
                            n.PropertyType ==SrchItemInfo.PropertyControlType.Tree 
                            //||n.PropertyType ==SrchItemInfo.PropertyControlType.TreeRB 

           select n;


           WhereSentenceWord.SentenceWordField[] arr = TreeFields.ToArray<WhereSentenceWord.SentenceWordField>();
           if (arr.Length == 0)
               return;

           List<string> s_arr = new List<string>();   
           for (int i = 0; i < arr.Length; i++)
           {
               WhereSentenceWord.WordFieldValue_TreeValue TreeValue1 = (WhereSentenceWord.WordFieldValue_TreeValue)arr[0].Value;
               string sOr = GetFieldOrConditions(arr[0]);
               string sOpType=string.Empty;
               if (arr[i].OperationType == WhereSentenceWord.SentenceWordOperatorTypes.EQUALLY)
                   sOpType = "=" + (TreeValue1.Value.ChildNodes.Count + 1).ToString();
               else
                   sOpType = ">1";

               string[] ScriptParams = new string[] { TreeValue1.Value.ID, sOr, sOpType };
               string FormatString = " SELECT ObjectID,'{0}' FROM " + Environment.NewLine +
                                       "(SELECT ObjectID , 1 as ItemCount from vSrchObjectPropertyTreeStorage " + Environment.NewLine +
                                        "WHERE " + Environment.NewLine +
                                        " {1} " + Environment.NewLine +
                                        ") as res " + Environment.NewLine +
                                        "GROUP BY res.Objectid " + Environment.NewLine +
                                        "HAVING count(res.objectid) {2} " + Environment.NewLine;
               string si = string.Format(FormatString, ScriptParams);
               s_arr.Add(si);
           }

           Writer.Append("--Отобрать значения деревянных справочников " + Environment.NewLine);
           Writer.Append("INSERT INTO @TreePropertyCollection  ");

           for (int j=0;j<s_arr.Count;j++)           
           {
               if (j != 0)
                   Writer.Append(" UNION ");
               Writer.Append(s_arr[j]+  Environment.NewLine);
               
           }
           ; 
           

       }

       /// <summary>
       /// Сформировать Корень дерева PropertyStorageTree
       /// </summary>
       /// <returns></returns>
       protected virtual void Script_PopulateRootOfPropertyTree_1(StringBuilder Writer)
       {
           string sAddWhere = String.Empty ;
           string[] ScriptParams = { Script_SrchRoot, Script_SrchObjectPropertyStorage, EntityTypeTagName, sAddWhere };
           string FormatString =
                                "select distinct  " + Environment.NewLine +
                                " ObjectID " + Environment.NewLine +
                                " into " + Environment.NewLine +
                                " {0} " + Environment.NewLine +
                                " from " + Environment.NewLine +
                                " {1} as Source " + Environment.NewLine +
                                " where " + Environment.NewLine +
                                " Source.TagName='{2}' " + Environment.NewLine+
                                " {3} " + Environment.NewLine;                              
            Writer.Append ( string.Format(FormatString, ScriptParams)); 
        
       }
              
       /// <summary>       
       /// Вернуть условие AND (tree.PropertyName + '.' + ObjectPropVal. PropertyName = 'Number' AND  ObjectPropVal.PropertyValue = '125')
       /// ObjectPropVal- добавляется если это like или =       
       /// Templates[0]=LikeTempl ;
       /// Templates[1]=EqualTempl ;
       /// DefTempl[2]=DefTempl;
       /// </summary>
       /// <returns></returns>
       private string Script_PopulatePropertyTree_2_GetFieldsRestriction(string [] Templates)
       {
           string LikeTempl = Templates[0];
           string EqualTempl = Templates[1];
           string DefTempl = Templates[2];
           string OnlyValueSrch = Templates[3];
                      
           StringBuilder sbw = new StringBuilder();
           //Список полей  - которых  касается запрос
           List<WhereSentenceWord.SentenceWordField> l = Sentence.GetAllWordFields();
           string sExpression = string.Empty;
               for (int i = 0; i < l.Count; i++)
               {
                   

                   switch (l[i].OperationType)
                   {
                       case WhereSentenceWord.SentenceWordOperatorTypes.LIKE:
                           sExpression=string.Format (LikeTempl ,new string[]{l[i].ID,l[i].Value .ToString ()});
                           break ;
                       case WhereSentenceWord.SentenceWordOperatorTypes .EQUALLY :
                           sExpression = string.Format(EqualTempl, new string[] { l[i].ID, l[i].Value.ToString() });
                           break;
                       default :
                           sExpression = string.Format(DefTempl, new string[] { l[i].ID, l[i].Value.ToString() });
                           break;
                   }

                   //Если - это непараметризированный запрос - шаблон один 
                   if (l[i].PropertyType == SrchItemInfo.PropertyControlType.OnlyValueSrch)
                       sExpression = string.Format(OnlyValueSrch, new string[] { l[i].Value.ToString() });
                       
                   
                   //Если - это дерево - шаблон один 
                   if (l[i].PropertyType == SrchItemInfo.PropertyControlType.Tree)
                       sExpression = string.Format(DefTempl, new string[] { l[i].ID });
                       

                   sbw.Append((i > 0 ? " OR " : "") + "(");
                   sbw.Append(sExpression);
                   sbw.Append(")");
               }
               return sbw.ToString();
           }       

       /// <summary>
       /// Заполнить дерево 
       /// </summary>
       /// <param name="Writer"></param>
       protected virtual void Script_PopulatePropertyTree_2(StringBuilder Writer)
       {
           #region GetNonIncludeObjects_Where
           ///выбрать из всех слов - слова 2 го уровня (объекты)- поля по которым идет запрос           
           Func<string> GetNonIncludeObjects_Where = delegate()
           {
               List<WhereSentenceWord.SentenceWordField> l = Sentence.GetAllWordFields();
               var Items =
               from n in l
               where
                   n.Level == 2
               select n;

               List<string> res = new List<string>();
               WhereSentenceWord.SentenceWordField[] WordFields = Items.ToArray<WhereSentenceWord.SentenceWordField>();
               PropertyInfo[] arr = EntityType.GetProperties();
               
               for (int i = 0; i < arr.Length; i++)
               {
                   
                   //Если - это примитив - не интересует
                   if(!ReflectionItem.MemberIsEntity (arr[i]))
                                     continue ;

                   //Если - это значение дерева - не интересует
                   if (ReflectionItem.MemberIsTreeValue(arr[i]))
                       continue;

                                      

                   bool bInclude = true;
                   foreach (WhereSentenceWord.SentenceWordField itm in WordFields)
                   {
                       if (itm.PropertyType == SrchItemInfo.PropertyControlType.Tree)
                           continue;

                       if (itm.ID.ToUpper().IndexOf(arr[i].Name.ToUpper())>-1)
                       {
                           bInclude = false;
                           break;
                       }
                   }

                   if (bInclude)                                          
                       res.Add(arr[i].Name);
                   
               }

               if (res.Count == 0)
                   return string.Empty;

               StringBuilder sb = new StringBuilder();
               for (int i = 0; i < res.Count; i++)
               {
                   if (i != 0)
                       sb.Append(",");

                   sb.Append("'" + res[i] + "'");

               }

               string[] Params = { sb.ToString() };
               string sFormat = " AND NOT srch.propertyname IN ({0})";
               return string.Format(sFormat, Params);
           };
#endregion
           
           string sPropWhere = string.Empty;
           string[] Templ = { "tree.PropertyName + '.' + ObjectPropVal. PropertyName = '{0}' AND  ObjectPropVal.PropertyValue like '%{1}%'",                                
                               "tree.PropertyName + '.' + ObjectPropVal. PropertyName = '{0}' AND  ObjectPropVal.PropertyValue ='{1}'",
                               "tree.PropertyName + '.' + ObjectPropVal. PropertyName = '{0}' ",
                               "ObjectPropVal.PropertyValue LIKE '%{0}%' "};
           sPropWhere = Script_PopulatePropertyTree_2_GetFieldsRestriction(Templ);

           sPropWhere = (sPropWhere.Length > 0 ? " AND " + sPropWhere : "");

           string[] Templ_ = { "VS. PropertyName = '{0}' AND  VS.PropertyValue like '%{1}%'",                                
                               "VS. PropertyName = '{0}' AND  VS.PropertyValue ='{1}'",
                               "VS. PropertyName = '{0}' ",
                               "VS. PropertyValue LIKE '%{0}%' "};
           string sPropWhere_ = Script_PopulatePropertyTree_2_GetFieldsRestriction(Templ_);
                  sPropWhere_ = (sPropWhere_.Length > 0 ? " AND " + sPropWhere_ : "");
           

           string sNonIncludeObjects_Where = GetNonIncludeObjects_Where();
           string[] ScriptParams = { Script_SrchRoot, 
                                    Script_SrchObjectPropertyStorage, 
                                    EntityTypeTagName, 
                                    sNonIncludeObjects_Where,
                                    sPropWhere,
                                    sPropWhere_};


           string FormatString =
                               " ;WITH " + Environment.NewLine +
                               " TREE (ObjectID, PropertyName,TxtPropertyName, PropertyValue,IsObject,LinkProp,ValueIsSqlFunction ) " + Environment.NewLine +
                               " AS ( " + Environment.NewLine +
                               "-- Корни дерева - записи свойства отвечающие уловиям отбора " + Environment.NewLine +
                               " SELECT " + Environment.NewLine +
                               " srch.ObjectID, srch.PropertyName, srch.TxtPropertyName, srch.PropertyValue,srch.isobject, srch.PropertyValue as LinkProp, srch.ValueIsSqlFunction " + Environment.NewLine +
                               " FROM " + Environment.NewLine +
                               "		{1}  srch       				" + Environment.NewLine +
                               "		JOIN {0} SrchRoot on SrchRoot.ObjectID =srch.ObjectID " + Environment.NewLine +
                               " WHERE " + Environment.NewLine +
                               "	srch.ISObject=1	{3}" + Environment.NewLine +
                               " UNION ALL" + Environment.NewLine +
                               "-- Подзапрос -  вниз" + Environment.NewLine +
                               " SELECT " + Environment.NewLine +
                               " T.ObjectID, " + Environment.NewLine +
                               " CAST((T.PropertyName +'.'+ V.PropertyName)   as varchar(255)) as PropertyName," + Environment.NewLine +
                               " CAST((T.TxtPropertyName +'.'+ V.TxtPropertyName)   as varchar(255)) as TxtPropertyName," + Environment.NewLine +                                                              
                               " V.PropertyValue,t.isobject, V.PropertyValue as LinkProp,  V.ValueIsSqlFunction " + Environment.NewLine +
                               " FROM {1} V " + Environment.NewLine +
                               " JOIN TREE T " + Environment.NewLine +
                               " ON " + Environment.NewLine +
                               " T.PropertyValue= V.objectid " + Environment.NewLine +
                               " and V.isobject=1 )     " + Environment.NewLine +
                               " -- Получаем дерево объектов - и цепляем описательные свойства" + Environment.NewLine +
                               " INSERT INTO @PopulatedObjectTree  SELECT " + Environment.NewLine +
                               " tree.ObjectId,  " + Environment.NewLine +
                               " CAST((tree.PropertyName +'.'+ ObjectPropVal.Propertyname )   as varchar(255)) as PropertyName , " + Environment.NewLine +
                               " CAST((tree.TxtPropertyName+'.'+ ObjectPropVal.TxtPropertyName  )   as varchar(255)) as TxtProperty," + Environment.NewLine +                                                              
                               " ObjectPropVal.PropertyValue, " + Environment.NewLine +
                               " ObjectPropVal.ValueIsSqlFunction " + Environment.NewLine +
                               " FROM tree  " + Environment.NewLine +
                               " join {1} ObjectPropVal on ObjectPropVal.objectid=tree.LinkProp" + Environment.NewLine +
                               " WHERE " + Environment.NewLine +                               
                               " ObjectPropVal.isobject=0" + Environment.NewLine +
                               "{4}" + Environment.NewLine +
                               " UNION " + Environment.NewLine +
                               " SELECT " + Environment.NewLine +
                               " VS.ObjectId,  " + Environment.NewLine +
                               " VS.PropertyName ," + Environment.NewLine +
                               " VS.TxtPropertyName,		" + Environment.NewLine +
                               " VS.PropertyValue, " + Environment.NewLine +
                               " VS.ValueIsSqlFunction " + Environment.NewLine +
                               " FROM " + Environment.NewLine +
                               " {1} VS JOIN {0} on VS.ObjectId={0}.ObjectId " + Environment.NewLine +
                               " WHERE " + Environment.NewLine +
                               " VS.isobject=0" + Environment.NewLine +
                               "{5}" ;                               

           Writer.Append(string.Format(FormatString, ScriptParams)); 


       }

       /// <summary>
       ///  В дереве - найти поодмножество id - отвечающих условиям отбора
       /// </summary>
       /// <param name="Writer"></param>
       protected virtual void Script_UpadateTreeValueProperty_3(StringBuilder Writer)
       {
           string FormatString ="--выделить подходящие древовидные свойства " + Environment.NewLine + 
                                "UPDATE @PopulatedObjectTree " + Environment.NewLine +
                                "SET PropertyValue='OK' " + Environment.NewLine +
                                "WHERE " + Environment.NewLine +
                                "PropertyValue IN (SELECT TreePropertyCollection.Objectid+'.'+TreePropertyCollection.TeeValueId from @TreePropertyCollection TreePropertyCollection)";
           Writer.Append(FormatString);
           
       }

       /// <summary>
       ///  1.Отобрать все ид - входящие в or предложения 
       /// </summary>
       /// <param name="Writer"></param>
       protected virtual void Script_PopulateTargetObjectSet_4(StringBuilder Writer)
       {
           //Условия для полей входящих в или
           string sWhere  = GetWhere(WhereSentenceWord.WhereSentenceWordTypes.OR);
           if (sWhere.Length > 0)
           {
               Writer.Append("INSERT INTO @SelectedObjectId  SELECT ObjectId from @PopulatedObjectTree WHERE " + sWhere + Environment.NewLine );
               Writer.Append("DELETE FROM @PopulatedObjectTree  WHERE ObjectId in (SELECT ObjectId FROM  @SelectedObjectId  )" + Environment.NewLine);
           }
           // Формирвания по условиям AND           
           sWhere  = GetWhere(WhereSentenceWord.WhereSentenceWordTypes.AND,SrchItemInfo.PropertyControlType .OnlyValueSrch);
           if(sWhere.Length >0)
           {
               List<WhereSentenceWord.SentenceWordField> l = Sentence.GetAllWordFields(WhereSentenceWord.WhereSentenceWordTypes.AND,SrchItemInfo.PropertyControlType.OnlyValueSrch);
               string[] ScriptParams = { sWhere, l.Count.ToString() };               
               string FormatString =
                                    "INSERT INTO @SelectedObjectId  " + Environment.NewLine +
                                    "SELECT " + Environment.NewLine +
                                    "Property.objectid	" + Environment.NewLine +
                                    "FROM " + Environment.NewLine +
                                    "@PopulatedObjectTree  Property" + Environment.NewLine +
                                    "WHERE {0} " + Environment.NewLine +
                                    "GROUP BY objectid " + Environment.NewLine +
                                    "HAVING count(objectid) ={1}"  + Environment.NewLine ;
               Writer.Append(string.Format(FormatString, ScriptParams));
           }

           
       }

       protected virtual void Script_PopulatePage_41(StringBuilder Writer)
       {
           string sWhere = GetWhere(SrchItemInfo.PropertyControlType.OnlyValueSrch);
           string[] ScriptParams = { "WHERE"+ sWhere};
           string FormatString =
               "IF exists(select * from @SelectedObjectId) "+ Environment.NewLine +
               "INSERT INTO  @SelectedObjectId_ " + Environment.NewLine +                               
                               "SELECT DISTINCT S.objectid  FROM @PopulatedObjectTree P join @SelectedObjectId S ON S.objectid=P.objectid  {0}" + Environment.NewLine+
               "ELSE " + Environment.NewLine +
               "INSERT INTO  @SelectedObjectId_ "+ Environment.NewLine +
               "SELECT DISTINCT P.objectid  FROM @PopulatedObjectTree P {0}	";
           
           Writer.Append(string.Format(FormatString, ScriptParams));
       }

       protected virtual void Script_PopulatePage_5(StringBuilder Writer)
       {
           bool bSelectedObjectId = Sentence.GetAllWordFields(SrchItemInfo.PropertyControlType.OnlyValueSrch).Count > 0;
           string[] ScriptParams = {StartPageRow.ToString (),
                                    EndPageRow.ToString ()  ,
                                    (bSelectedObjectId?"@SelectedObjectId_":"@SelectedObjectId")
                                   };
           string FormatString =
                                "DECLARE @Page_SelectedObjectId TABLE (ObjectId varchar(36))" + Environment.NewLine +
                                "INSERT INTO @Page_SelectedObjectId SELECT ObjectId	FROM  " + Environment.NewLine +
                                "(SELECT  " + Environment.NewLine +
                                "ObjectId, " + Environment.NewLine +
                                "ROW_NUMBER()  OVER (ORDER BY ObjectId) AS row_num " + Environment.NewLine +
                                "FROM " + Environment.NewLine +
                                "{2}) " + Environment.NewLine +
                                "AS Page 			" + Environment.NewLine +
                                "WHERE row_num BETWEEN  {0} and {1}" + Environment.NewLine;
           Writer.Append(string.Format(FormatString, ScriptParams));
       }

       protected virtual void Script_GetObjects_6(StringBuilder Writer)
       {
           string FormatString =
                               "DECLARE @OutputRwCnt AS INT " + Environment.NewLine +
                               "SELECT @OutputRwCnt=COUNT(*) FROM @Page_SelectedObjectId      " + Environment.NewLine +
                               "DECLARE @res AS XML " + Environment.NewLine +
                               "SELECT @res=(SELECT vSrchStorageLinks.ObjectXML.query('.')  FROM @Page_SelectedObjectId T join vSrchStorageLinks on vSrchStorageLinks.ID=T.objectId FOR XML PATH(''), TYPE, ELEMENTS , ROOT('EntityList'))" + Environment.NewLine +
                               "IF not(@res is null)   SET @res.modify('insert attribute OutputRwCnt {sql:variable(''@OutputRwCnt'')} into (/EntityList)[1]')" + Environment.NewLine +
                               "select @res " + Environment.NewLine;
           Writer.Append(FormatString); 
       }

       private string GetWhere(WhereSentenceWord.WhereSentenceWordTypes Type)
       {
           return GetWhere(Type, string.Empty); 
       }

       private string GetWhere(SrchItemInfo.PropertyControlType FieldType  )
       {
           StringBuilder sbw = new StringBuilder();
           List<WhereSentenceWord.SentenceWordField> l = Sentence.GetAllWordFields(FieldType);

           for (int i = 0; i < l.Count; i++)
           {
               sbw.Append((i > 0 ? " OR " : "") + "(");
               sbw.Append(l[i].GetSql());
               sbw.Append(")");
           }
           string s = sbw.ToString();           
           return s;

       }
       
       private string GetWhere(WhereSentenceWord.WhereSentenceWordTypes Type,string SourceName)
       {

           //Форммируем запрос по полям или
           StringBuilder sbw = new StringBuilder();
           List<WhereSentenceWord.SentenceWordField> l = Sentence.GetAllWordFields(Type);
           for (int i = 0; i < l.Count; i++)
           {
               sbw.Append((i > 0 ? " OR " : "") + "(");
               sbw.Append(l[i].GetSql());
               sbw.Append(")");
           }
           string s= sbw.ToString();
           if(SourceName.Equals (string .Empty ))
                return s;
           else
           {
               s = s.Replace("PropertyName", SourceName + ".PropertyName");
               s = s.Replace("PropertyValue", SourceName + ".PropertyValue");
               return s;
           }
       }

       /// <summary>
       /// Строит запрос where - исключая уловия только по значению поля
       /// </summary>
       /// <param name="Type"></param>
       /// <param name="SourceName"></param>
       /// <returns></returns>
       private string GetWhere(WhereSentenceWord.WhereSentenceWordTypes Type,SrchItemInfo.PropertyControlType ExludeField)
       {            
           //Формируем запрос по полям или
           StringBuilder sbw = new StringBuilder();
           List<WhereSentenceWord.SentenceWordField> l = Sentence.GetAllWordFields(Type, ExludeField);
           for (int i = 0; i < l.Count; i++)
           {               
               sbw.Append((i > 0 ? " OR " : "") + "(");
               sbw.Append(l[i].GetSql());
               sbw.Append(")");
           }
           string s = sbw.ToString();           
           return s;
           
       }

   }

}
